package experiments.data;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Random;
import java.util.UUID;

import nosql.MysqlConnector;
import nosql.NosqlException;
import nosql.query.NosqlStatement;

import org.json.simple.JSONObject;
import org.json.simple.parser.ParseException;

/**
 * Generate the synthetic dataset according to the properties files.
 * @author yjian004
 *
 */
public class DataGenerator {
	private String filePath;
	private String urlPrefix;
	private String username;
	private String password;
	
	private MysqlConnector connector;
	private NosqlStatement statement;
	
	public DataGenerator(String path, String urlPrefix, String username, String password){
		this.filePath = path;
		this.urlPrefix = urlPrefix;
		this.username = username;
		this.password = password;
	}
	
	public void generateVerticalPartitioning() throws IOException, ParseException, NosqlException, SQLException{
		
		File file = new File(filePath);
		if(file.isDirectory()){
			File[] files = file.listFiles();
			for(File f : files){
				String fileName = f.getName();
				String extension = fileName.substring(fileName.lastIndexOf("."));
				if(extension.equals(".properties")){
					generateSingleFileVerticalPartitioning(f);
				}
			}
		}
		else if(file.isFile()){
			String fileName = file.getName();
			String extension = fileName.substring(fileName.lastIndexOf("."));
			if(extension.equals("properties")){
				generateSingleFileVerticalPartitioning(file);
			}
		}
	}
	
	protected void generateSingleFileVerticalPartitioning(File file) throws IOException, ParseException, NosqlException, SQLException{
		String fileName = file.getName();
		fileName = fileName.substring(0, fileName.indexOf("."));
		
		
		try {
			String createDB = "create database if not exists `" + fileName + "`";
			String strDBUrl = "jdbc:mysql://localhost/";
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(strDBUrl, username, password);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(createDB);
			stmt.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		connector = MysqlConnector.getInstance(urlPrefix + fileName, username, password);
		statement = new NosqlStatement(connector);
		
		FileInputStream fis = new FileInputStream(file);
		Properties propertyFile = new Properties();
		propertyFile.load(fis);
		
		int dataSize = Integer.parseInt(propertyFile.getProperty("data_size"));
		double denseRate = Double.parseDouble(propertyFile.getProperty("dense_rate"));
		String items = propertyFile.getProperty("items");
		String values = propertyFile.getProperty("values");
		String[] itemArr = items.split(",");
		String[] valueArr = values.split(",");
		
		int length = itemArr.length;
		int valueLength = valueArr.length;
		int avgLength = (int)(length * denseRate);
		
		System.out.println("Data size:" + dataSize + ", Item:" + length + ", Dense rate:" + denseRate);
		
		Random random = new Random();
		int cur = 0;
		long startTime = System.currentTimeMillis();
		for(int i = 0; i < dataSize; ++i){
			//	randomly pick transactionLength items from candidates
			String recordStr = "";
			JSONObject json = new JSONObject();
			json.put("id", i);
			for(int j = 0; j < length; ++j){
				float coin = Math.abs(random.nextFloat() % 1);
				if(coin < denseRate){
					int randomValueIdx = Math.abs(random.nextInt() % (valueLength - 1));
					int propertyValue = Integer.parseInt(valueArr[randomValueIdx]);
					json.put(itemArr[j], propertyValue);
				}
			}
//			System.out.println(json);
			statement.insert(json.toJSONString());
			if(++cur % 1000 == 0){
				long currentTime = System.currentTimeMillis();
				System.out.println("There are " + cur + " records inserted, cost time:" + (currentTime - startTime)/1000 + " seconds.");
			}
		}
		
		connector.close();
	}
	
	public void generateWideTables() throws IOException, ParseException, NosqlException, SQLException{
		
		File file = new File(filePath);
		if(file.isDirectory()){
			File[] files = file.listFiles();
			for(File f : files){
				String fileName = f.getName();
				String extension = fileName.substring(fileName.lastIndexOf("."));
				if(extension.equals(".properties")){
					generateSingleFileWideTable(f);
				}
			}
		}
		else if(file.isFile()){
			String fileName = file.getName();
			String extension = fileName.substring(fileName.lastIndexOf("."));
			if(extension.equals("properties")){
				generateSingleFileWideTable(file);
			}
		}
	}
	
	protected void generateSingleFileWideTable(File file) throws IOException, SQLException{
		String fileName = file.getName();
		fileName = fileName.substring(0, fileName.indexOf("."));
		
		FileInputStream fis = new FileInputStream(file);
		Properties propertyFile = new Properties();
		propertyFile.load(fis);
		
		try {
			String createDBstr = "create database if not exists `vwide" + fileName + "`";
			String strDBUrl = "jdbc:mysql://localhost/";
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(strDBUrl, username, password);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(createDBstr);
			stmt.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		String items = propertyFile.getProperty("items");
		String[] itemArr = items.split(",");
		String sqlGenerateTableStatement = "create table if not exists vwideTable(entityId varchar(128) not null,";
		String sqlInsertTableStatment = "insert into vwideTable(entityId,";
		String sqlInsertTableValueStatement = "values(?,";
		for(int i = 0; i < itemArr.length; ++i){
			if(i != itemArr.length - 1){
				sqlGenerateTableStatement += "`" + itemArr[i] + "` varchar(255), ";
				sqlInsertTableStatment += "`" + itemArr[i] + "`,";
				sqlInsertTableValueStatement += "?,";
			}
			else{
				sqlGenerateTableStatement += "`" + itemArr[i] + "` varchar(255)";
				sqlInsertTableStatment += "`" + itemArr[i] + "`";
				sqlInsertTableValueStatement += "?";
			}
		}
		
		sqlGenerateTableStatement += ", primary key (`entityId`), key `idx_i` (`entityId`))Engine=innoDB default charset=utf8;";
		sqlInsertTableValueStatement += ");";
		sqlInsertTableStatment += ") " + sqlInsertTableValueStatement;
		
		System.out.println(sqlGenerateTableStatement);
		System.out.println(sqlInsertTableStatment);
		
		Connection conn = null;
		
		try {
			String strDBUrl = "jdbc:mysql://localhost/" + "vwide" + fileName;
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(strDBUrl, username, password);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(sqlGenerateTableStatement);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} 
		
		float denseRate = Float.parseFloat(propertyFile.getProperty("dense_rate"));
		Random random = new Random();
		int dataSize = Integer.parseInt(propertyFile.getProperty("data_size"));
		PreparedStatement prepStmt = conn.prepareStatement(sqlInsertTableStatment);
		int count = 0;
		long startTime = System.currentTimeMillis();
		for(int i = 0; i < dataSize; ++i){
			String uuid = UUID.randomUUID().toString();
			prepStmt.setString(1, "" + uuid);
			for(int j = 0; j < itemArr.length; ++j){
				float p = random.nextFloat() % 1;
				if(p < denseRate){
					String value = String.valueOf(random.nextInt() % 10);
					prepStmt.setString(j + 2, value);
				}
				else{
					prepStmt.setString(j + 2, null);
				}
			}
			prepStmt.addBatch();
			++count;
			if(count % 1000 == 0){
				try{
					prepStmt.executeBatch();
					prepStmt.clearBatch();
					long currentTime = System.currentTimeMillis();
					System.out.println("Insert " + count + " records, cost " + (currentTime - startTime) / 1000 + " seconds.");
				} catch(BatchUpdateException be){
					be.printStackTrace();
					System.exit(1);
				}
			}
		}
		
		Statement stmt = conn.createStatement();
		for(int i = 0; i < itemArr.length; ++i){
			String strAddIndex = "alter table vwidetable add index idx_" + itemArr[i] + "(`"+itemArr[i]+"`)";
			try{
				stmt.executeUpdate(strAddIndex);
				long currentTime = System.currentTimeMillis();
				System.out.println("Added index idx_" + itemArr[i] + ", cost time:" + (currentTime - startTime) / 1000 + " seconds.");
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		stmt.close();
		
		prepStmt.close();
		conn.close();
	}
	
	public void generateTripleTable() throws IOException, SQLException{
		File file = new File(filePath);
		if(file.isDirectory()){
			File[] files = file.listFiles();
			for(File f : files){
				String fileName = f.getName();
				String extension = fileName.substring(fileName.lastIndexOf("."));
				if(extension.equals(".properties")){
					generateSingleTripleTable(f);
				}
			}
		}
		else if(file.isFile()){
			String fileName = file.getName();
			String extension = fileName.substring(fileName.lastIndexOf("."));
			if(extension.equals("properties")){
				generateSingleTripleTable(file);
			}
		}
	}
	
	protected void generateSingleTripleTable(File file) throws IOException, SQLException{
		String fileName = file.getName();
		fileName = fileName.substring(0, fileName.indexOf("."));
		
		FileInputStream fis = new FileInputStream(file);
		Properties propertyFile = new Properties();
		propertyFile.load(fis);
		
		try {
			String createDBstr = "create database if not exists `triple" + fileName + "`";
			String strDBUrl = "jdbc:mysql://localhost/";
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(strDBUrl, username, password);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(createDBstr);
			stmt.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		String strGenerateTable = "create table if not exists triple(entityId varchar(128), property varchar(128), value text, primary key (entityId, property), key idx_id(entityId), key idx_property (property))Engine=InnoDB default charset=utf8;";
		
		Connection conn = null;
		try {
			String strDBUrl = "jdbc:mysql://localhost/triple" + fileName;
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(strDBUrl, username, password);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(strGenerateTable);
			stmt.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		int dataSize = Integer.parseInt(propertyFile.getProperty("data_size"));
		String item = propertyFile.getProperty("items");
		float denseRate = Float.parseFloat(propertyFile.getProperty("dense_rate"));
		String[] itemArr = item.split(",");
		
		String strInsertStmt = "insert into triple(entityId, property, value) values(?,?,?);";
		PreparedStatement prepStmt = conn.prepareStatement(strInsertStmt);
		
		Random random = new Random();
		long startTime = System.currentTimeMillis();
		for(int i = 0; i < dataSize; ++i){
			String uuid = UUID.randomUUID().toString();
			for(int j = 0; j < itemArr.length; ++j){
				float p = random.nextFloat() % 1;
				if(p < denseRate){
					int value = random.nextInt() % 5 + 5;
					prepStmt.setString(1, "" + uuid);
					prepStmt.setString(2, itemArr[j]);
					prepStmt.setString(3, String.valueOf(value));
					prepStmt.addBatch();
				}
			}
			
			if(i % 1000 == 0){
				long currentTime = System.currentTimeMillis();
				prepStmt.executeBatch();
				prepStmt.clearBatch();
				System.out.println("There are " + i + " records inserted, cost time " + (currentTime - startTime)/1000 + " seconds.");
			}
		}
		
		
		prepStmt.close();
		conn.close();
		
		
	}
}
